package com.zbec.mdrs.repository.jpa;

import com.zbec.mdrs.entity.entrustedLoan.EntrustedLoan;
import com.zbec.mdrs.vo.EntrustedLoanVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Created by fxl on 2015/10/17.
 */
public interface EntrustedLoanDao extends PagingAndSortingRepository<EntrustedLoan, Long>, JpaSpecificationExecutor<EntrustedLoan> {

    List<EntrustedLoan> findByHistoryId(Long historyId);

    List<EntrustedLoan> findByHistoryIdAndOrgId(Long historyId,Long orgId);

    @Query("select new com.zbec.mdrs.vo.EntrustedLoanVo(el.id,el.sjsj,el.jrjgbm,el.wtrzzjgdm,el.jkrzzjgdm,el.bz," +
            "el.ll,el.ywfse,el.ywye,el.dkffrq,el.dkdqrq,el.jjh,el.qymc,el.source,el.recordStatus,el.reportStatus," +
            "el.existError, el.errorDesc, el.missedField,el.recordId) from EntrustedLoan el  " +
            "where (''= :sjsj or el.sjsj = :sjsj) "+
            "and (''= :recordStatus or el.recordStatus = :recordStatus) " +
            "and (''= :existError or el.existError = :existError)  ")
    Page<EntrustedLoanVo> findAllEntrustedLoan(@Param("sjsj") String sjsj, @Param("recordStatus") String recordStatus, @Param("existError") String existError, Pageable pageable);

    @Query("select new com.zbec.mdrs.vo.EntrustedLoanVo(el.id,el.sjsj,el.jrjgbm,el.wtrzzjgdm,el.jkrzzjgdm,el.bz," +
            "el.ll,el.ywfse,el.ywye,el.dkffrq,el.dkdqrq,el.jjh,el.qymc,el.source,el.recordStatus,el.reportStatus," +
            "el.existError, el.errorDesc, el.missedField,el.recordId) from EntrustedLoan el  " +
             "where (''= :historyId or el.history.id = :historyId) ")
    Page<EntrustedLoanVo> findEntrustedLoanByHistroy(@Param("historyId")String historyId,Pageable pageable);

    @Query("select l from EntrustedLoan l  " +
            "where (''= :sjsj or l.sjsj = :sjsj) "+
            "and (''= :recordStatus or l.recordStatus = :recordStatus) " +
            "and (''= :existError or l.existError = :existError) order by  l.wtrzzjgdm")
    List<EntrustedLoan>  findEntrustedLoanForExport(@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus,@Param("existError")String existError);



    @Query( "select max(sjsj) from EntrustedLoan where sjsj < :sjsj")
    String getMaxEntrustedLoanFny(@Param("sjsj")String sjsj);

    @Query("select l from EntrustedLoan l where l.recordStatus !=3 and l.recordId = :recordId and l.sjsj = :sjsj ")
    EntrustedLoan getEntrustedLoanFromDrmap(@Param("recordId")Long recordId,@Param("sjsj")String sjsj);

    @Query("select l from EntrustedLoan l  " +
            "where l.sjsj = :sjsj  and l.recordStatus = :recordStatus  and  l.reportStatus <> :reportStatus " +
            "and (''= :jrjgbm or l.jrjgbm = :recordStatus) order by  l.wtrzzjgdm  ")
    List<EntrustedLoan> getActiveEntrustedLoanByYwMonth(@Param("sjsj")String sjsj ,@Param("recordStatus")String recordStatus,
                                                        @Param("reportStatus")String reportStatus,@Param("jrjgbm")String jrjgbm);


    @Query("select l from EntrustedLoan l  " +
            "where l.sjsj = :sjsj  and l.recordStatus <>  :recordStatus  and  l.jjh = :jjh  ")
    List<EntrustedLoan> findUpdateEntrustedLoan(@Param("jjh")String jjh ,@Param("sjsj")String sjsj,@Param("recordStatus")String recordStatus);


}
